import sqlite3
from datetime import datetime

def get_db_connection():
    conn = sqlite3.connect('database.db')
    conn.row_factory = sqlite3.Row
    return conn

def get_brands():
    conn = get_db_connection()
    brands = conn.execute('SELECT * FROM brands').fetchall()
    conn.close()
    return brands

def get_firmwares_by_brand(brand_id):
    conn = get_db_connection()
    firmwares = conn.execute('''
        SELECT * FROM firmwares 
        WHERE brand_id = ? 
        ORDER BY upload_date DESC
    ''', (brand_id,)).fetchall()
    conn.close()
    return firmwares

def get_all_firmwares():
    conn = get_db_connection()
    firmwares = conn.execute('SELECT * FROM firmwares ORDER BY upload_date DESC').fetchall()
    conn.close()
    return firmwares

def insert_firmware(brand_id, version, uploader, changelog, firmware_path, timestamp):
    conn = get_db_connection()
    conn.execute('''
        INSERT INTO firmwares 
        (brand_id, version, upload_date, uploader, firmware_path, changelog)
        VALUES (?, ?, ?, ?, ?, ?)
    ''', (brand_id, version, timestamp, uploader, firmware_path, changelog))
    conn.commit()
    conn.close()

def remove_firmware(firmware_id):
    conn = get_db_connection()
    firmware = conn.execute('SELECT firmware_path FROM firmwares WHERE id = ?', (firmware_id,)).fetchone()
    conn.execute('DELETE FROM firmwares WHERE id = ?', (firmware_id,))
    conn.commit()
    conn.close()
    return firmware['firmware_path'] if firmware else None

def get_firmware_path(firmware_id):
    conn = get_db_connection()
    firmware = conn.execute('SELECT firmware_path FROM firmwares WHERE id = ?', (firmware_id,)).fetchone()
    conn.close()
    return firmware['firmware_path'] if firmware else None

def get_stats():
    conn = get_db_connection()
    brands_count = conn.execute('SELECT COUNT(*) FROM brands').fetchone()[0]
    firmwares_count = conn.execute('SELECT COUNT(*) FROM firmwares').fetchone()[0]
    uploaders_count = conn.execute('SELECT COUNT(DISTINCT uploader) FROM firmwares').fetchone()[0]
    conn.close()
    return brands_count, firmwares_count, uploaders_count

def get_brand_latest_version(brand_id):
    conn = get_db_connection()
    version = conn.execute('''
        SELECT version FROM firmwares 
        WHERE brand_id = ? 
        ORDER BY upload_date DESC LIMIT 1
    ''', (brand_id,)).fetchone()
    conn.close()
    return version[0] if version else "无版本"

def insert_brand(brand_name):
    conn = get_db_connection()
    conn.execute('INSERT INTO brands (name) VALUES (?)', (brand_name,))
    conn.commit()
    conn.close()